﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TrainigWorkshop.Model;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace TrainigWorkshop.DAL
{
    public class StudentRespository : IStudentRespository
    {
        public SqlConnection conn;
        public StudentRespository(SqlConnection conn)
        {
            this.conn= conn;
        }
        public void InsertStudentInfo(StudentModel student)
        {
            try
            {
                conn.Open();
                string SQL = "insert into Student(StudentName,StudentGender,ClassId) values(@StudentName,@StudentGender,@classId)";
                SqlCommand cmd = new SqlCommand(SQL, conn);
                cmd.CommandType = CommandType.Text;
                SqlParameter[] paras = new SqlParameter[]
                    {
                      new SqlParameter("StudentName", student.StudentName),
                      new SqlParameter("StudentGender", student.StudentGender), 
                      new SqlParameter("classId", student.grades.ClassId) 
                    };
                cmd.Parameters.AddRange(paras);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            
        }

        public void UpdateStudentInfo(StudentModel student)
        {
              try
                {
                    conn.Open();
                    string SQL = "update Student set  StudentName=@StudentName,StudentGender=@StudentGender, ClassId=@classId where StudentId=@StudentId";
                    SqlCommand cmd = new SqlCommand(SQL, conn);
                    cmd.CommandType = CommandType.Text;
                    SqlParameter[] paras = new SqlParameter[]
                    {
                      new SqlParameter("@StudentId",student.StudentId),
                      new SqlParameter("@StudentName", student.StudentName),
                      new SqlParameter("@StudentGender", student.StudentGender), 
                      new SqlParameter("@classId", student.grades.ClassId)
                    };
                    cmd.Parameters.AddRange(paras);

                    cmd.ExecuteNonQuery();
                }
               finally
              {
                conn.Close();
              }

            }
        

        public void DeleteStudentInfo(StudentModel student)
        {
                try
                {
                    conn.Open();
                    string SQL = "delete from Student where StudentId=@StudentId";
                    SqlCommand cmd = new SqlCommand(SQL, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("StudentId", student.StudentId));

                    cmd.ExecuteNonQuery();
                }
               finally
              {
                 conn.Close();
              }
       }
    

        public StudentModel GetStudentInfoById(StudentModel student)
        {
                try
                {
                    // set value
                    string SQL = "select StudentId,StudentName,StudentGender,ClassId from Student  where StudentId = @StudentId";
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = SQL;
                    cmd.Parameters.Add(new SqlParameter("@StudentId", student.StudentId));

                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    reader.Read();

                    //get value
                    student.StudentId = reader.GetInt32(0);
                    student.StudentName = reader.GetString(1);
                    student.StudentGender = reader.GetString(2);
                    student.grades.ClassId = reader.GetInt32(3);
                    reader.Close();
                    return student;
                }
               finally
              {
                conn.Close();
              }
            }

        public List<StudentModel> GetStudentInfo()
        {
            List<StudentModel> result = new List<StudentModel>();
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand("select StudentId,StudentName,StudentGender,ClassName from Student,Class  where Student.ClassId=Class.ClassId", conn);
                comm.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(ds, "result");
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Grade grade = new Grade();

                    StudentModel studentCourseDetail = new StudentModel(grade) { };

                    studentCourseDetail.StudentId = Convert.ToInt32(row[0]);
                    studentCourseDetail.StudentName = row[1].ToString().Trim();
                    studentCourseDetail.StudentGender = row[2].ToString().Trim();
                    grade.ClassName = row[3].ToString().Trim();

                    result.Add(studentCourseDetail);

                }
                return result;
            }
            finally
            {
                conn.Close();
            }

        }
        }

    }

